import pymysql
from common.log import logger


class SqlOperate():
    def __init__(self, ip_addr: str = "127.0.0.1", db_name: str = "interface_test", username: str = "root",
                 password: str = "root", port: int = 3306, link_type=0, charset="utf8mb4"):
        """
        :param ip_addr: 数据库服务器IP地址
        :param username: 访问数据库用户名
        :param password: 访问数据库密码
        :param db_name: 访问数据库名
        :param port: 访问数据库端口号
        :param link_type: 数据库操作结果的数据类型，元组or字典，默认字典，即0
        :param charset: 访问数据库编码格式
        """
        self.db_name = db_name
        self.ip_addr = ip_addr
        try:
            if link_type == 0:
                self.connect = pymysql.connect(host=ip_addr, user=username, password=password, database=db_name,
                                               port=int(port), cursorclass=pymysql.cursors.DictCursor, charset=charset,
                                               connect_timeout=100)
            else:
                self.connect = pymysql.connect(host=ip_addr, user=username, password=password, database=db_name,
                                               port=port, charset=charset, connect_timeout=100)
            self.cursor = self.connect.cursor()
        except Exception as e:
            logger.error(f"[{self.__class__.__name__}] {e}")

    def __del__(self):
        # def close(self):
        if self.cursor != None:
            self.cursor.close()
        if self.connect != None:
            self.connect.close()

    def select_one(self, statement: str) -> dict:
        """
        :param statement: select语句
        :return: 字典类型，code表示操作数据库的结果代码，message表示操作数据库的结果信息，data表示操作数据库的数据
            其中，data值为字典类型
        """
        try:
            num = self.cursor.execute(statement)
            if num > 0:
                data = self.cursor.fetchone()
            else:
                data = []
            result = {"code": "000", "message": "查询单条数据成功", "data": data}
        except pymysql.Error as e:
            logger.error(f"[{self.__class__.__name__}] {e}")
            logger.error(f"[{self.__class__.__name__}] Failed to SELECT data from database '{self.db_name}'.")
            logger.error(f"[{self.__class__.__name__}] Select sql: {statement}")
            result = {"code": "900", "message": "查询单条数据失败", "data": []}
        return result

    def select_all(self, statement: str):
        """
        :param statement: select语句
        :return: 字典类型，code表示操作数据库的结果代码，message表示操作数据库的结果信息，data表示操作数据库的数据
            其中，data值为列表类型，每个元素是字典类型
        """
        try:
            num = self.cursor.execute(statement)
            if num > 0:
                data = self.cursor.fetchall()
            else:
                data = []
            result = {"code": "000", "message": "查询所有数据成功", "data": data}
        except pymysql.Error as e:
            logger.error(f"[{self.__class__.__name__}] {e}")
            logger.error(f"[{self.__class__.__name__}] Failed to SELECT data from database '{self.db_name}'.")
            logger.error(f"[{self.__class__.__name__}] Select sql: {statement}")
            result = {"code": "900", "message": "查询所有数据失败", "data": []}
        return result

    def insert_more(self, statement: str, args: list):
        """
        :param statement: insert语句
        :param args: 要插入的数据，列表类型，每个元素是字典类型，
                    如[{"name":"Wendy", "age":26, "job":"singer"}, {"name":"Diane", "age":29}]
        :return: 字典类型，code表示操作数据库的结果代码，message表示操作数据库的结果信息，data表示操作数据库的数据
        """
        try:
            rows_affect = self.cursor.executemany(statement, args)
            self.connect.commit()
            result = {"code": "000", "message": "插入多条数据成功", "data": rows_affect}
        except pymysql.Error as e:
            logger.error(f"[{self.__class__.__name__}] {e}")
            logger.error(f"[{self.__class__.__name__}] Failed to insert more data to database '{self.db_name}'")
            logger.error(f"[{self.__class__.__name__}] Insert sql: {statement}")
            self.connect.rollback()
            result = {"code": "900", "message": "插入多条数据失败", "data": []}
        return result

    def operate_sql(self, statement):
        """
        该方法可替代insert_one, insert_more,
        :param statement: sql语句，可以是插入insert、更新update、删除delete语句
        :return: 字典类型，code表示操作数据库的结果代码，message表示操作数据库的结果信息，data表示操作数据库的数据
        """
        try:
            rows_affect = self.cursor.execute(statement)
            self.connect.commit()
            result = {"code": "000", "message": "通用操作数据库成功", "data": []}
        except pymysql.Error as e:
            logger.error(f"[{self.__class__.__name__}] {e}")
            logger.error(f"[{self.__class__.__name__}] Failed to operate sql on database '{self.db_name}'")
            logger.error(f"[{self.__class__.__name__}] Operate sql: {statement}")
            self.connect.rollback()
            result = {"code": "900", "message": "通用操作数据库失败", "data": []}
        return result


if __name__ == "__main__":
    # sql_ins=SqlOperate(ip_addr="jetflow.mynetgear.com", db_name="daxing", port=53306)
    sql_ins = SqlOperate(ip_addr="10.0.0.233", db_name="demo", port=3306)
